package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.beans.Votes;

@Repository (value="votedao")
public class VoteDAOImpl implements VoteDAO{
	private JdbcTemplate jt;
	@Autowired
	public VoteDAOImpl(DataSource data)
	{
		this.jt=new JdbcTemplate(data);
	}

	@Override
	public int add(final Votes vote) {
		// TODO Auto-generated method stub
		int id=0;
		if(vote!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL VOTE_ADD(?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, vote.getUserID());
							stm.setInt(2, vote.getPhotoID());
							stm.setInt(3, vote.getCompetitionID());
							stm.setString(4, vote.getVoteIP());
							stm.setInt(5, 1);
							stm.registerOutParameter(6, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(6);
								obj[1]=rs;
								return obj;
							}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delete(final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL VOTE_DELETE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Votes> getvoteByCompetition(final int cid) {
		// TODO Auto-generated method stub
		if(cid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL VOTE_GETBYCOMPETITION(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, cid);
							stm.registerOutParameter(2, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(2);
								obj[1]=null;
								List<Votes> votes=new ArrayList<Votes>();
								while (rs.next())
								{
									Votes vote =new Votes();
									vote.setCompetitionID(rs.getInt("competitionid"));
									vote.setId(rs.getInt("id"));
									vote.setPhotoID(rs.getInt("photoid"));
									vote.setUserID(rs.getInt("userid"));
									vote.setVotedate(rs.getTimestamp("votedate"));
									vote.setVoteIP(rs.getString("voteip"));
									vote.setVoteRank(rs.getInt("voterank"));
									votes.add(vote);
								}
								obj[1]=votes;
								return obj;
							}});
			return (List<Votes>) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Votes> getvoteByPhoto(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL VOTE_GETBYPHOTO(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							stm.registerOutParameter(2, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(2);
								obj[1]=null;
								List<Votes> votes=new ArrayList<Votes>();
								while (rs.next())
								{
									Votes vote =new Votes();
									vote.setCompetitionID(rs.getInt("competitionid"));
									vote.setId(rs.getInt("id"));
									vote.setPhotoID(rs.getInt("photoid"));
									vote.setUserID(rs.getInt("userid"));
									vote.setVotedate(rs.getTimestamp("votedate"));
									vote.setVoteIP(rs.getString("voteip"));
									vote.setVoteRank(rs.getInt("voterank"));
									votes.add(vote);
								}
								obj[1]=votes;
								return obj;
							}});
			return (List<Votes>) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Votes> getvoteByUser(final int uid) {
		// TODO Auto-generated method stub
		if(uid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL VOTE_GETBYUSER(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, uid);
							stm.registerOutParameter(2, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(2);
								obj[1]=null;
								List<Votes> votes=new ArrayList<Votes>();
								while (rs.next())
								{
									Votes vote =new Votes();
									vote.setCompetitionID(rs.getInt("competitionid"));
									vote.setId(rs.getInt("id"));
									vote.setPhotoID(rs.getInt("photoid"));
									vote.setUserID(rs.getInt("userid"));
									vote.setVotedate(rs.getTimestamp("votedate"));
									vote.setVoteIP(rs.getString("voteip"));
									vote.setVoteRank(rs.getInt("voterank"));
									votes.add(vote);
								}
								obj[1]=votes;
								return obj;
							}});
			return (List<Votes>) obj[1];
		}
		return null;
	}

}
